﻿Imports MySql.Data.MySqlClient
Imports System.Configuration

Public Class MysqlDataBaseConnection
    Implements IDisposable

    Public Sub New()
    End Sub

#Region "Forked Function (using pooling)"

    ''' <summary>
    ''' Run SQL select statement using a newly created db connection object. Displose connection object after running select.
    ''' </summary>
    ''' <param name="sql">SQL in string format.</param>
    ''' <returns>Result of SQL statement in DataSet</returns>
    ''' <remarks></remarks>
    Public Shared Function RunSelect(ByVal sql As String) As DataSet

        sql = CleanUpStatement(sql)

        Using newConnection = New MySqlConnection()

            Dim result As DataSet = New DataSet
            result.Locale = System.Globalization.CultureInfo.InvariantCulture
            
            Try

                Dim dbString As String = ThisApplication.ConnectionString
                If Not String.IsNullOrEmpty(dbString) Then
                    newConnection.ConnectionString = dbString
                End If
                newConnection.Open()

                Dim adapter As MySqlDataAdapter = New MySqlDataAdapter(sql, newConnection)
                adapter.Fill(result)

                Return result

            Catch ex As SqlClient.SqlException
                Throw ex
            End Try

        End Using

    End Function

    ''' <summary>
    ''' Run SQL update statement using a newly created db connection object. Displose connection object after running update.
    ''' </summary>
    ''' <param name="sql">SQL in string format.</param>
    ''' <returns>Result of SQL statement in DataSet</returns>
    ''' <remarks></remarks>
    Public Shared Function RunNonQuery(ByVal sql As String) As Integer

        sql = CleanUpStatement(sql)

        Using newConnection = New MySqlConnection()

            Using cmd = New MySqlCommand(sql, newConnection)

                Try

                    Dim dbString As String = ThisApplication.ConnectionString
                    If Not String.IsNullOrEmpty(dbString) Then
                        newConnection.ConnectionString = dbString
                    End If

                    newConnection.Open()

                    Dim rows As Integer = cmd.ExecuteNonQuery()
                    Return rows

                Catch ex As SqlClient.SqlException
                    Throw ex
                End Try

            End Using

        End Using

    End Function

    ''' <summary>   
    ''' Run sql list, for transaction, Temp solution
    ''' </summary>   
    ''' <param name="SQLStringList">Sql list</param>        
    Public Shared Sub RunSqlTransaction(ByVal SQLStringList As List(Of String))

        Using newConnection = New MySqlConnection()

            Dim dbString As String = ThisApplication.ConnectionString
            If Not String.IsNullOrEmpty(dbString) Then
                newConnection.ConnectionString = dbString
            End If

            newConnection.Open()

            Dim command As MySqlCommand = New MySqlCommand
            command.Connection = newConnection

            Dim transaction As MySqlTransaction = newConnection.BeginTransaction()
            command.Transaction = transaction

            Try
                For Each sql As String In SQLStringList
                    sql = CleanUpStatement(sql)
                    command.CommandText = sql
                    command.ExecuteNonQuery()
                Next
                transaction.Commit()
            Catch ex As SqlClient.SqlException
                transaction.Rollback()
                Throw ex
            End Try
        End Using
    End Sub

#End Region

    ''' <summary>
    ''' Reduce extra spaces into single spaces. Also replace tab and LFCR characters as space.
    ''' </summary>
    ''' <param name="sql">SQL statement to be cleaned</param>
    ''' <returns>Cleaned SQL statement</returns>
    ''' <remarks></remarks>
    Public Shared Function CleanUpStatement(ByVal sql As String) As String

        ' trip tab characters, then CrLf characters
        sql = sql.Replace(ControlChars.Tab, " ").Replace(ControlChars.CrLf, " ")

        ' trim spaces within in sql string
        While sql.IndexOf("  ", System.StringComparison.Ordinal) >= 0
            sql = sql.Replace("  ", " ")
        End While

        ' return santized string
        Return sql

    End Function

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
